package com.sunwayworld.dev.core.db.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

import com.sun.org.apache.regexp.internal.RESyntaxException;
import com.sunwayworld.dev.core.db.ColumnModel;
import com.sunwayworld.dev.core.db.PrimaryKeyModel;
import com.sunwayworld.dev.core.db.TableModel;
import com.sunwayworld.dev.core.valueobject.Field;
import com.sunwayworld.dev.util.BeanUtils;
import com.sunwayworld.dev.util.StringDevUtil;


public class MySQLTableEngine implements TableEngine {

	private Connection conn = null;

	private String catalog;

	private String schema;

	private String tableName;

	public MySQLTableEngine(String tableName, Connection conn) {
		this.tableName = tableName;
		this.conn = conn;
	}

	public ColumnModel[] generateColumnModel() {
		// TODO Auto-generated method stub

		Statement stmt = null;
		ResultSet rs = null;
		ColumnModel[] columnModels = new ColumnModel[0];
		String sqlStr = "select * from " + tableName;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sqlStr);
			ResultSetMetaData metaDate = rs.getMetaData();
			int cnt = metaDate.getColumnCount();
			columnModels = new ColumnModel[cnt];
			for (int i = 0; i < cnt; i++) {
				columnModels[i] = new ColumnModel();
				columnModels[i].setColumn_name(metaDate.getColumnName(i + 1));
				columnModels[i].setData_type(metaDate.getColumnTypeName(i + 1));
				columnModels[i].setTable_name(tableName);

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return columnModels;
	}

	public TableModel generateTableModel() {
		ResultSet rs = null;
		Statement stmt = null;
		TableModel tableModel = new TableModel();
		try {

			stmt = conn.createStatement();
			rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
			if (rs != null && rs.next()) {
				byte[] a=rs.getBytes(2);
				String create = new String(a,"utf-8");
				//System.out.println(create);
				analyzeComment(create);
				String comment = parse(create);
				tableModel.setComments(comment);
			}

			tableModel.setPkModel(this.generatePKModel());
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			cleanup(stmt, rs);
		}
		return tableModel;
	}

	public PrimaryKeyModel[] generatePKModel() {
		PrimaryKeyModel[] pkModels = null;
		ResultSet rs = null;
		try {
			DatabaseMetaData dmeta = conn.getMetaData();
			// System.out.print(tableName);
			rs = dmeta.getPrimaryKeys(catalog, schema, tableName);
			ArrayList<PrimaryKeyModel> alist = new ArrayList<PrimaryKeyModel>();

			while (rs.next()) {

				PrimaryKeyModel pkModel = new PrimaryKeyModel();
				BeanUtils.copyProperties(rs, pkModel);
				alist.add(pkModel);
			}
			pkModels = new PrimaryKeyModel[alist.size()];
			for (int i = 0; i < alist.size(); i++) {
				pkModels[i] = (PrimaryKeyModel) alist.get(i);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			cleanup(null, rs);
		}
		return pkModels;

	}

	public Field[] getFieldsFromDB() {

		ColumnModel[] colModel = this.generateColumnModel();
		if (colModel == null) {
			return null;
		}

		Field[] fields = new Field[colModel.length];
		for (int i = 0, n = colModel.length; i < n; i++) {
			fields[i] = new Field();

			fields[i].setName(StringDevUtil.toLowerCase(colModel[i]
					.getColumn_name()));

			fields[i].setNotation(StringDevUtil.toString(colModel[i]
					.getComments()));
			if("".equals(fields[i].getNotation())){
				fields[i].setNotation(fields[i].getName());
			}

			if ("N".equals(colModel[i].getNullable())) {
				fields[i].setIsNull("true");
			} else {
				fields[i].setIsNull("false");
			}

			String oracle_type = StringDevUtil.toString(colModel[i]
					.getData_type());

//			System.out.println("===========" + oracle_type);
			if ("VARCHAR".equals(oracle_type)) {
				fields[i].setType("String");
			} else if ("DATE".equals(oracle_type)) {
				fields[i].setType("java.util.Date");
			} else if ("NUMBER".equals(oracle_type)) {
				if ("0".equals(colModel[i].getData_Scale())) {
					fields[i].setType("Long");
				} else {

					fields[i].setType("java.math.BigDecimal");
				}

			}
			if ("INTEGER".equals(oracle_type)) {
				fields[i].setType("Long");
			} else {

				fields[i].setType("String");
			}

		}
		return fields;
	}

	public String getCatalog() {
		return catalog;
	}

	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public String getSchema() {
		return schema;
	}

	public void setSchema(String schema) {
		this.schema = schema;
	}

	public String getTableName() {
		// TODO Auto-generated method stub
		return tableName;
	}

	public void setTableName(String tableName) {
		// TODO Auto-generated method stub
		this.tableName = tableName;
	}

	private void cleanup(PreparedStatement pstmt, ResultSet rs) {
		try {
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		} catch (Exception e) {

		}
		try {
			if (rs != null)
				rs.close();
			rs = null;
		} catch (Exception ex) {

		}

	}

	private void cleanup(Statement stmt, ResultSet rs) {
		try {
			if (stmt != null)
				stmt.close();
			stmt = null;
		} catch (Exception e) {

		}
		try {
			if (rs != null)
				rs.close();
			rs = null;
		} catch (Exception ex) {

		}

	}

	private String parse(String all) {
		String comment = null;
		int index = all.indexOf("COMMENT='");
		if (index < 0) {
			return "";
		}
		comment = all.substring(index + 9);
		comment = comment.substring(0, comment.length() - 1);
		return comment;
	}
	
	private void analyzeComment(String commentstr) throws RESyntaxException{
		commentstr=commentstr.substring(commentstr.indexOf("(")+2,commentstr.lastIndexOf(")"));
		System.out.println(commentstr);
		String[] temps=commentstr.split(",");
		for(int i=0;i<temps.length;i++){
			String t=StringDevUtil.toString(temps[i]);
			
			String[] t2=t.split("'*'");
			for(int j=0;j<t2.length;j++){
				System.out.println(t2[j]);	
			}
			System.out.println("===========");	
		}
	}

}
